package com.api.shuigui.service.impl;

import cn.hutool.core.convert.Convert;
import com.api.shuigui.pojo.DocContent;
import com.api.shuigui.pojo.PageBean;
import com.api.shuigui.service.SpecialDocContentService;
import com.api.shuigui.utils.DocUtil;
import com.wbi.util.Util;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import weaver.conn.RecordSet;
import weaver.general.BaseBean;
import weaver.general.MD5;

import java.io.IOException;
import java.text.SimpleDateFormat;
import java.time.Year;
import java.util.*;

public class SpecialDocContentServiceImpl implements SpecialDocContentService {
    private static final Logger LOGGER = LoggerFactory.getLogger("SpecialDocContentServiceImpl");
    BaseBean bb = new BaseBean();

    @Override
    public Map<String, Object> getDocContent(Map<String, Object> params) throws IOException {
        //处理结果
        Map<String, Object> result = new HashMap<>();
        //获取的当前的页码
        int pageNum = Integer.parseInt(params.get("pageNum").toString());
        //获取每页显示的条数
//        int pageSize = Integer.parseInt(params.get("pageSize").toString());
        //获取目录
        String category = Util.null2String(params.get("category"));
        //获取搜索框输入的数据
        String search = Util.null2String(params.get("search"));
        if (pageNum <= 0) {
            pageNum = 1;
        }
        Integer year = Convert.toInt(params.get("year"), 5);
        Integer pageSize = Convert.toInt(params.get("pageSize"), 7);
        List<Integer> recentYears = getRecentYears(year);

        PageBean<DocContent> pb = new PageBean<>();
        //封装参数
        pb.setCurrentPage(pageNum);
        pb.setRows(pageSize);
        //查询总条数
        int totalSize = findTotal(category, search);
        pb.setTotalCount(totalSize);
        //查询数据
        int start = (pageNum - 1) * pageSize;
        List<DocContent> list = getAllDoc(start, pageSize, category, search);
        pb.setList(list);
        result.put("data", pb);
        bb.writeLog("结果是1-----------" + result);
        return result;
    }

    private int findTotal(String category, String search) {
        RecordSet re = new RecordSet();
        String dbType = re.getDBType();
        StringBuilder stringBuffer = new StringBuilder();
        String sql;
        if (dbType.equals("sqlserver")) {
            //Sql Server
            sql = "select count(1) from DocDetail a left join DocSecCategory b on a.seccategory = b.id where b.id in("+category+") ";
        } else {
            //Mysql
            sql = "select count(1) from DocDetail a inner join DocSecCategory b on a.seccategory = b.id inner join docdetailcontent c on c.docid = a.id where b.id in("+category+")  and a.doctype = 1 ";
        }
        stringBuffer.append(sql);
        //如果有搜索条件
        if (!"".equals(search)) {
            if (dbType.equals("sqlserver")) {
                //Sql Server
                stringBuffer.append(" and (a.docsubject like '%").append(search).append("%' or a.doccontent like '%").append(search).append("%')");
            } else {
                //Mysql
                stringBuffer.append(" and (a.docsubject like '%").append(search).append("%' or c.doccontent like '%").append(search).append("%')");
            }
        }
        bb.writeLog("统计sql是----" + stringBuffer);
        boolean b = re.executeQuery(stringBuffer.toString());
        int total = 0;
        if (b && re.next()) {
            total = re.getInt(1);
        }
        return total;
    }

    private List<DocContent> getAllDoc(int pageNum, int pageSize, String category, String search) throws IOException {
        //初始化数据库连接
        RecordSet re = new RecordSet();
        //得到数据库类型
        String dbType = re.getDBType();
        //初始化sql语句
        StringBuilder stringBuffer = new StringBuilder();
        //获取当前时间
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String currentDate = sdf.format(new Date());
        String sql = "";
        if (dbType.equals("sqlserver")) {
            //Sql Server
//            sql = "select top " + pageSize + " * from (select a.id,a.docsubject,a.doccontent,a.doccreatedate,a.doccreatetime,ROW_NUMBER() " +
//                    " over (ORDER BY a.doccreatedate desc,a.doccreatetime desc) as rowNumber from DocDetail a inner join DocSecCategory b on a.seccategory= b.id " +
//                    "where  b.id in (" + category + ") and a.doctype = 1 and a.docstatus in(1,2)";
            sql = "SELECT a.id, c.doccontent, a.docsubject, a.doccreatedate, a.doccreatetime FROM (SELECT d.*, ROW_NUMBER() OVER(PARTITION BY YEAR(d.doccreatedate) ORDER BY d.doccreatedate DESC) AS rn FROM DocDetail d INNER JOIN DocSecCategory b ON d.seccategory = b.id WHERE b.id IN (" +category + ")  AND d.doctype = 1 AND d.docstatus IN (1, 2)) a INNER JOIN docdetailcontent c ON c.docid = a.id WHERE a.rn <=  "+pageSize+"  ORDER BY YEAR(a.doccreatedate) DESC, a.doccreatedate DESC";

        } else {
            //Mysql
//            sql = "select a.id,c.doccontent,a.docsubject,a.doccreatedate,a.doccreatetime from DocDetail a inner join DocSecCategory b on a.seccategory= b.id inner join " +
//                    " docdetailcontent c on c.docid = a.id" +
//                    " where  b.id in (" + category + ") and a.doctype = 1 and a.docstatus in(1,2)";

            sql = "SELECT a.id, c.doccontent, a.docsubject, a.doccreatedate, a.doccreatetime FROM ( SELECT  d.*, ROW_NUMBER() OVER( PARTITION BY EXTRACT( YEAR FROM d.doccreatedate) ORDER BY d.doccreatedate DESC) AS rn FROM DocDetail d INNER JOIN DocSecCategory b ON d.seccategory = b.id WHERE b.id in (" +category + ")  AND d.doctype = 1  AND d.docstatus IN (1, 2)) a INNER JOIN docdetailcontent c ON c.docid = a.id WHERE a.rn <= "+pageSize+" ORDER BY EXTRACT(YEAR FROM a.doccreatedate) DESC, a.doccreatedate DESC";

        }
        LOGGER.info("dbType = {}",dbType);
        LOGGER.info("execute sql {}",sql);
        stringBuffer.append(sql);

        // 不需要搜索条件 和 分页
        /**
        //如果有搜索条件
        if (!"".equals(search)) {
            if (dbType.equals("sqlserver")) {
                //Sql Server
                stringBuffer.append(" and (a.docsubject like '%").append(search).append("%' or a.doccontent like '%").append(search).append("%')");
            } else {
                //Mysql
                stringBuffer.append(" and (a.docsubject like '%").append(search).append("%' or c.doccontent like '%").append(search).append("%')");
            }
        }
        if (dbType.equals("sqlserver")) {
            //Sql Servel
            stringBuffer.append(" )c where rowNumber > ").append(pageNum).append(" ORDER BY a.istop desc, c.doccreatedate desc,c.doccreatetime desc");
        } else {
            //Mysql
            stringBuffer.append("ORDER BY a.istop desc, a.doccreatedate desc,a.doccreatetime desc limit ").append(pageNum).append(",").append(pageSize);
        }
     **/

        boolean b = re.execute(stringBuffer.toString());
        List<DocContent> list = new ArrayList<>();
        while (b && re.next()) {
            DocContent doc = new DocContent();
            //解析doccontent中的字符
            String content = Util.null2String(re.getString("doccontent"));
            String result = "";
            if (!"".equals(content)) {
                result = getDocContent(content, "span");
                if ("".equals(result)) {
                    //如果span标签内容为空，则取p标签
                    result = getDocContent(content, "p");
                }
            }
            doc.setDocContent(result);
            doc.setDocSubject(Util.null2String(re.getString("docsubject")));
            doc.setDocCreateDate(Util.null2String(re.getString("doccreatedate")));
            Integer id = re.getInt("id");
            doc.setId(id);
            String imgUrl = "";
            //如果文档中有信息，则查找是否有图片
            //通过id查询文档对应的图片路径和名称
            String msql;
            if (dbType.equals("sqlserver")) {
                //Sql Server
                msql = "select top 1 i.imagefilename,i.filerealpath from DocImageFile d left join ImageFile i on i.imagefileid = d.imagefileid where d.docid = ?";
            } else {
                //Mysql
                msql = "select i.imagefilename,i.filerealpath from DocImageFile d left join ImageFile i on i.imagefileid = d.imagefileid where d.docid = ? limit 1";
            }
            RecordSet re1 = new RecordSet();
            boolean b1 = re1.executeQuery(msql, id);
            //获得ecology路径
            //有文件
            if (b1 && re1.next()) {
                String filename = re1.getString("imagefilename");
                String filepath = re1.getString("filerealpath");
                //判断是否是文档类附件
                String filetype = Util.null2String(filename.substring(filename.lastIndexOf(".") + 1));
                //得到最终存储的图片名称
                String realName = id + "_" + new MD5().getMD5ofStr(filename) + "." + filetype;
                if (filetype.equals("png") || filetype.equals("jpg") || filetype.equals("jpeg") ||
                        filetype.equals("gif")
                ) {
                    //是图片
                    String savePath = "interface/imgs/doc/" + id + "/";
                    //判断路径下是否存在该文件
                    if (DocUtil.isCategoryByName(realName, savePath)) {
                        //存在
                        imgUrl = "/" + savePath + realName;
                    } else {
                        //不存在则解压文件获取图片
                        imgUrl = DocUtil.readZipFile(filepath, filename, realName, savePath);
                    }
                }
            } else if (!"".equals(DocUtil.ContentIsImgurl(content))) {
                //查看是否存在带链接的图片
                imgUrl = DocUtil.ContentIsImgurl(content);
            }
            doc.setImgUrl(imgUrl);
            list.add(doc);
        }
        bb.writeLog("结果是-----------" + list);
        return list;
    }

    /**
     * 提取html中指定标签的值 span or p
     *
     * @param content
     * @param tag
     * @return
     */
    private String getDocContent(String content, String tag) {
        try {
            //将string文本转换成html文本
            Document doc = Jsoup.parse(content);
            //得到全部p标签
            List<Element> links = doc.getElementsByTag(tag);
            //实例化stringbuffer
            StringBuilder buffer = new StringBuilder();
            for (Element link : links) {
                //将文本提取出来
                buffer.append(link.text().trim());
            }
            return buffer.toString().trim();
        } catch (Exception e) {
            bb.writeLog(e.getMessage());
            return content;
        }
    }

    /**
     * 获取当前年份及前n年
     * @param count 需要的年份数量
     * @return 降序排列的年份列表
     */
    public  List<Integer> getRecentYears(int count) {
        List<Integer> years = new ArrayList<>();
        int currentYear = Year.now().getValue();

        for (int i = 0; i < count; i++) {
            years.add(currentYear - i);
        }

        return years;
    }
}
